package cn.hfatec.shms.web.vip.vipXxb.service;

import java.io.File;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


import cn.hfatec.JsonResponse;
import cn.hfatec.shms.advice.annotation.MysqlDataSource;
import cn.hfatec.shms.advice.annotation.OracleDataSource;
import cn.hfatec.shms.constants.ShmsProperties;
import cn.hfatec.shms.cache.CodeInfoCache;
import cn.hfatec.shms.constants.ShmsConstant;
import cn.hfatec.shms.enums.CodeType;
import cn.hfatec.shms.exception.NotAuthException;
import cn.hfatec.shms.system.codeInfo.model.CodeInfo;
import cn.hfatec.shms.system.user.model.User;
import cn.hfatec.shms.utils.ExcelUtil;
import cn.hfatec.shms.web.td.tdHzb.model.TdHzb;
import cn.hfatec.shms.web.td.tdHzb.model.TdHzbParams;
import cn.hfatec.shms.web.td.tdMxb.model.TdMxb;
import cn.hfatec.shms.web.vip.vipXxb.model.VipCheck;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import cn.hfatec.base.BaseDao;
import cn.hfatec.Page;
import cn.hfatec.Table;
import cn.hfatec.shms.web.vip.vipXxb.model.VipXxb;
import cn.hfatec.shms.web.vip.vipXxb.dao.VipXxbDao;
import cn.hfatec.shms.web.vip.vipXxb.model.VipXxbParams;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


/**
 * @author hfatec
 * @version 1.0
 *          <p/>
 *          the JAVA code is generate by middlegen
 */
@Service("VipXxbService")
public class VipXxbService {

    public static final String BEAN_ID = "VipXxbService";

    //注入Dao层
    @Resource(name = "VipXxbDao")
    private VipXxbDao vipXxbDao;

    //注入BaseDao
    @Resource(name = "BaseDao")
    private BaseDao baseDao;
    @Resource(name = "shmsProperties")
    private ShmsProperties shmsProperties;

    @Transactional
    public static String encodeFilename(HttpServletRequest request, String fileName) {
        String rtn = "";
        try {
            String userAgent = request.getHeader("USER-AGENT");
            String new_filename = URLEncoder.encode(fileName, "UTF8");

            rtn = "filename=\"" + new_filename + "\"";

            if (userAgent != null) {
                userAgent = userAgent.toLowerCase();

                if (userAgent.indexOf("msie") != -1) {
                    rtn = "filename=\"" + new_filename + "\"";
                } else if (userAgent.indexOf("opera") != -1) {
                    rtn = "filename*=UTF-8''" + new_filename;
                } else if (userAgent.indexOf("safari") != -1) {
                    rtn = "filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO8859-1") + "\"";
                } else if (userAgent.indexOf("applewebkit") != -1) {
                    new_filename = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
                    rtn = "filename=\"" + new_filename + "\"";
                } else if (userAgent.indexOf("mozilla") != -1) {
                    rtn = "filename*=UTF-8''" + new_filename;
                }
            }
        } catch (UnsupportedEncodingException e) {
            try {
                rtn = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            } catch (UnsupportedEncodingException localUnsupportedEncodingException1) {
            }
        }
        return rtn;
    }

    /**
     * 查询数据
     *
     * @param vipXxbParams
     * @param page
     */
    @Transactional
    @MysqlDataSource
    public Table queryVipXxbTable(VipXxbParams vipXxbParams, Page page) {

        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("params", vipXxbParams);
        return baseDao.queryTableByPage("queryVipXxb", params, page);
    }

    /**
     * 查询数据
     *
     *
     *
     */
    @Transactional
    @MysqlDataSource
    public List<VipXxb> listVipXxbTable() {
        return vipXxbDao.listSelect();
    }

    /**
     * 插入数据
     *
     * @param vipXxb
     */
    @Transactional
    @MysqlDataSource
    public void saveVipXxb(VipXxb vipXxb, User user) {
        if (vipXxb.getId() != null) {
            VipXxb result = getVipXxb(vipXxb.getId());
            if (result != null) {
                vipXxb.setCreatTime(new Date());
                vipXxb.setCreatName(user.getUserName());
                vipXxbDao.update(vipXxb);
            } else {
                vipXxb.setCreatTime(new Date());
                vipXxb.setCreatName(user.getUserName());
                vipXxbDao.insert(vipXxb);
            }
        } else {
            vipXxb.setCreatTime(new Date());
            vipXxb.setCreatName(user.getUserName());
            vipXxbDao.insert(vipXxb);
        }
    }

    /**
     * 插入数据
     *
     * @param vipXxb
     */
    @Transactional
    @MysqlDataSource
    public void updateVipXxb(VipXxb vipXxb) {
        vipXxbDao.update(vipXxb);
    }

    /**
     * 删除数据
     *
     * @param id
     */
    @Transactional
    @MysqlDataSource
    public void deleteVipXxb(Integer id) {

        vipXxbDao.delete(id);
    }

    /**
     * 删除数据
     *
     * @param vipXxbList
     */
    @Transactional
    @MysqlDataSource
    public void deleteVipXxbBatch(List<String> vipXxbList) {
        for (String id : vipXxbList) {
            deleteVipXxb(Integer.valueOf(id));
        }
    }

    /**
     * 根据ID获取数据
     *
     * @param id
     */
    @Transactional
    @MysqlDataSource
    public VipXxb getVipXxb(Integer id) {

        return vipXxbDao.getVipXxbById(id);
    }

    /**
     * 根据ID获取数据
     *
     * @param cardno
     */
    @Transactional
    @OracleDataSource
    public VipXxb getVipSum(String cardno) {

        return vipXxbDao.getVipXxbSum(cardno);
    }

    /**
     * 根据ID获取数据
     *
     * @param cardno
     * @param fgrDoctor
     */
    @Transactional
    @OracleDataSource
    public VipXxb getVipUpdate(String cardno,String fgrDoctor) {

        return vipXxbDao.getVipXxbUpdate(cardno,fgrDoctor);
    }

    /**
     * 根据ID获取数据
     *
     * @param cardno
     * @param packageName
     */
    @Transactional
    @OracleDataSource
    public VipXxb getVipCardno(String cardno, String packageName) {

        return vipXxbDao.getVipXxbByCardno(cardno, packageName);
    }

    /**
     * 根据ID获取数据
     *
     * @param cardno
     */
    @Transactional
    @MysqlDataSource
    public VipXxb getVipAmb(String cardno) {

        return vipXxbDao.getVipXxbByAmb(cardno);
    }

    /**
     * 根据ID获取数据
     *
     * @param firstReport
     */
    @Transactional
    @MysqlDataSource
    public VipXxb getVipFirstReport(String firstReport) {

        return vipXxbDao.getVipXxbByFirstReport(firstReport);
    }

    /**
     * 导入天道数据
     *
     * @param path
     * @param request
     * @param response
     * @return
     * @throws java.io.IOException
     */
    @Transactional
    @MysqlDataSource
    public String importCust(String path, HttpServletRequest request, HttpServletResponse response) throws Exception {
        String filePath = path.substring(path.indexOf("/upload") + 7);
        User user = (User) request.getSession().getAttribute(ShmsConstant.LOGIN_USER_SESSION_NAME);
        if (user == null) {
            throw new NotAuthException("用户未授权");
        }
        String uploadPath = shmsProperties.getUploadPath();
        File file = new File(uploadPath + File.separator + filePath);
        boolean flag = false;
        String rule = "^1[358]\\d{9}$";
        String errorMsg = "";
        Workbook workbook = null;
        workbook = WorkbookFactory.create(file);
        Sheet sh = workbook.getSheetAt(0);//
        List<VipCheck> potentialCusts = this.buildCustWithAjaxUpload(sh);//
        if (potentialCusts != null && potentialCusts.size() > 0) {
            for (VipCheck vipCheck : potentialCusts) {
                //判读是否为有效数据

                if (vipCheck.isValid()) {
                    VipXxb vipXxb = vipCheck.getVipXxb();
                    User user1 = new User();

                    String firstReport = null;
                    VipXxb vipXxbxh = getVipCardno(vipXxb.getCardno(), null);
                    if (vipXxbxh != null) {
                        vipXxb.setPackageName(vipXxbxh.getPackageName());
                        vipXxb.setStartTime(vipXxbxh.getStartTime());
                        vipXxb.setEndTime(vipXxbxh.getEndTime());
                        vipXxb.setPackagePrice(vipXxbxh.getPackagePrice());
                        vipXxb.setTjDate(vipXxbxh.getTjDate());
                        vipXxb.setProblem(vipXxbxh.getProblem());
                        vipXxb.setFgrDoctor(vipXxbxh.getFgrDoctor());
                        vipXxb.setMeetTime(vipXxbxh.getMeetTime());
                        vipXxb.setMeetType(vipXxbxh.getMeetType());
                        vipXxb.setZgDoor(vipXxbxh.getZgDoor());
                        vipXxb.setAsgender(vipXxbxh.getAsgender());
                        vipXxb.setCooperationDegree(vipXxbxh.getCooperationDegree());
                        vipXxb.setLastDate(vipXxbxh.getLastDate());
                        vipXxb.setClientNo(vipXxbxh.getClientNo());
                        vipXxb.setFirstReport(vipXxbxh.getFirstReport());
                        vipXxb.setLastReport(vipXxbxh.getLastReport());
                        firstReport = vipXxbxh.getFirstReport();
                    }
                    VipXxb vipXxbAmb = getVipAmb(vipXxb.getCardno());
                    if (vipXxbAmb != null) {
                        vipXxb.setPromoteDoctor(vipXxbAmb.getPromoteDoctor());
                        vipXxb.setAmbId(vipXxbAmb.getAmbId());
                    }
                    VipXxb vipXxbTj = getVipFirstReport(firstReport);
                    if (vipXxbTj != null) {
                        vipXxb.setMzId(vipXxbTj.getMzId());
                        vipXxb.setTjId(vipXxbTj.getTjId());
                        vipXxb.setProblem(vipXxbTj.getProblem());
                    }
                    saveVipXxb(vipXxb, user1);
                    flag = true;
                } else {//无效数据
                    errorMsg += "第" + vipCheck.getRowNumber() + "行的姓名或录入日期为空，不能导入!";
                }
            }
        } else {
            return new JsonResponse<String>(JsonResponse.RETURN_FAILURE, "导入的文件无符合数据").toString();
        }

        if (flag) {
            if ("".equals(errorMsg)) {
                return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "导入成功!" + errorMsg).toString();
            }
            return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "部分导入成功!" + errorMsg).toString();
        } else {
            return new JsonResponse<String>(JsonResponse.RETURN_SUCCESS, "导入失败!" + errorMsg).toString();
        }

    }

    /**
     * 读取潜在客户导入的数据
     *
     * @param sh
     * @return
     * @throws Exception
     */
    @Transactional
    @MysqlDataSource
    private List<VipCheck> buildCustWithAjaxUpload(Sheet sh) throws Exception {
        List<VipCheck> checks = new ArrayList<VipCheck>();
        int rows = sh.getLastRowNum() + 1 - sh.getFirstRowNum(); // 总行数
        List<CodeInfo> sexList = CodeInfoCache.getCodeInfo(CodeType.SEX);
        List<CodeInfo> cardList = CodeInfoCache.getCodeInfo(CodeType.CARD_TYPE);
        List<CodeInfo> boxList = CodeInfoCache.getCodeInfo(CodeType.BOX_TYPE);
        List<CodeInfo> coopeateList = CodeInfoCache.getCodeInfo(CodeType.COOPEATE_DEGREE);
        List<CodeInfo> interventionList = CodeInfoCache.getCodeInfo(CodeType.INTERVENTION_TYPE);
        List<CodeInfo> feelList = CodeInfoCache.getCodeInfo(CodeType.FEEL_TYPE);
        List<CodeInfo> dosageList = CodeInfoCache.getCodeInfo(CodeType.DOSAGE_SIZE);
        List<CodeInfo> vipList = CodeInfoCache.getCodeInfo(CodeType.VIP_RAND);
        List<CodeInfo> contactList = CodeInfoCache.getCodeInfo(CodeType.CONTACT_TIME_TYPE);
        List<CodeInfo> problemList = CodeInfoCache.getCodeInfo(CodeType.PROBLEM_TYPE);
        ExcelUtil excelUtil = new ExcelUtil();
        for (int i = 1; i < rows; i++) {
            boolean flag = true;//数据是否有效(客户姓名，手机号存在)
            boolean flag2 = false;//判断是否该行有数据
            VipXxb vipXxb = new VipXxb();
            VipCheck vipCheck = new VipCheck();
            vipCheck.setRowNumber(i + 1);
            vipCheck.setValid(true);
            String str = "";//下拉框
            // HSSFRow row = sh.getRow(i);//excel97-2003写法
            Row row = sh.getRow(i);
//            int cols = row.getLastCellNum() + 1 - row.getFirstCellNum(); // 该行的总列数
            Iterator cells = row.cellIterator();
            int j = 0;
            while (cells.hasNext()) {
                //判断行数据的客户姓名和电话是否为空，某一项为空则不执行循环
                if (StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, 0)) || StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, 1))) {
                    for (int r = 0; r <= 10; r++) {
                        if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, r))) {
                            if (!flag2) {
                                vipCheck.setValid(false);
                                flag2 = true;
                            }
                        }
                    }
                    flag = false;
                    break;
                }

//                HSSFCell cell = (HSSFCell) cells.next();//excel97-2003写法
                XSSFCell cell = (XSSFCell) cells.next();

                if (j == 0) {
                    //卡号
                    if (StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        throw new Exception("第【" + i + "】行数据，姓名不能为空！");
                    }
                    vipXxb.setClientName(ExcelUtil.getStringRowCellValue(row, j));
                }
                if (j == 1) {
                    //
                    if (StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        throw new Exception("第【" + i + "】行数据，身份证不能为空！");
                    }
                    vipXxb.setCardno(ExcelUtil.getStringRowCellValue(row, j));
                } else if (j == 2) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setVipId(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 3) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setMobile(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 4) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setVice(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 5) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setRequirements(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 6) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setContactName(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 7) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setContactPhone(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 8) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : contactList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setContactTime(c.getValue());
                            }
                        }
                    }
                } else if (j == 9) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setMedication(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 10) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : dosageList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setDrug(c.getValue());
                            }
                        }
                    }
                } else if (j == 11) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : feelList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setFeel(c.getValue());
                            }
                        }
                    }
                } else if (j == 12) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setAddress(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 13) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setDetailedAddress(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 14) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setNote(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 15) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setSyDoor(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 16) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : vipList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setProbability(c.getValue());
                            }
                        }
                    }

                } else if (j == 17) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : cardList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setTurnCard(c.getValue());
                            }
                        }
                    }

                } else if (j == 18) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : dosageList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setDosage(c.getValue());
                            }
                        }
                    }
                } else if (j == 19) {
                    str = excelUtil.getStringRowCellValue(row, j);
                    if (!StringUtils.isEmpty(str)) {
                        for (CodeInfo c : feelList) {
                            if (c.getContent().equals(str)) {
                                vipXxb.setAfterFeeling(c.getValue());
                            }
                        }
                    }

                } else if (j == 20) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setTransferRecord(ExcelUtil.getStringRowCellValue(row, j));
                    }
                } else if (j == 21) {
                    // 年龄
                    if (!StringUtils.isEmpty(excelUtil.getStringRowCellValue(row, j))) {
                        vipXxb.setTransferAttention(ExcelUtil.getStringRowCellValue(row, j));
                    }
                }
                j++;
            }
            if (flag) {
//                 list.add(cust);
                vipCheck.setVipXxb(vipXxb);
                checks.add(vipCheck);
            } else {
//                vipCheck.setCustom(cust);
                if (flag2) {
                    checks.add(vipCheck);
                }
            }
        }
        return checks;
    }

    /**
     * 导出
     *
     * @param
     */
    @Transactional
    public String exportExcel(VipXxbParams vipXxbParams, HttpServletRequest request, HttpServletResponse response) {
        try {
            String clientFileName = encodeFilename(request, System.currentTimeMillis() + "");
            response.reset();
            response.setHeader("Content-Disposition", "attachment; " + clientFileName + ".xls");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream os = response.getOutputStream();
            List dataList = vipXxbDao.queryVipXxb(vipXxbParams);
            List nameList = new ArrayList();
            nameList.add("身份证号");
            nameList.add("姓名");
            nameList.add("卡别");
            nameList.add("开始日期");
            nameList.add("结束日期");
            nameList.add("卡别金额");
            nameList.add("会员档案号");
            nameList.add("体检卡号");
            nameList.add("门诊ID");
            nameList.add("体检日期");
            nameList.add("主要问题");
            nameList.add("联系电话");
            nameList.add("促会人");
            nameList.add("副会");
            nameList.add("入会需求");
            nameList.add("应急联系人");
            nameList.add("应急联系人电话");
            nameList.add("适合联系时间");
            nameList.add("档案盒资料");
            nameList.add("入会时用药");
            nameList.add("入会时药量");
            nameList.add("入会时主观感受");
            nameList.add("地址所属区域");
            nameList.add("详细地址");
            nameList.add("备注");
            nameList.add("分管医生");
            nameList.add("最近见面时间");
            nameList.add("最近联系时间及方式");
            nameList.add("首次入会日期");
            nameList.add("应上门次数");
            nameList.add("余上门次数");
            nameList.add("是否过期");
            nameList.add("配合度");
            nameList.add("管理期最后一次体检日期");
            nameList.add("会员续会概率");
            nameList.add("可能转卡类");
            nameList.add("管理后药量");
            nameList.add("管理后主观感受");
            nameList.add("会员转接记录");
            nameList.add("转接注意事项");
            this.exportExcel(nameList, dataList, os);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "";
    }

    /**
     * @return HSSFWorkbook
     */
    public HSSFWorkbook createWorkbook(List fieldName, List fieldData) {
        List<CodeInfo> vipTypeList = CodeInfoCache.getCodeInfo(CodeType.VIP_TYPE);
        List<CodeInfo> overdueTypeList = CodeInfoCache.getCodeInfo(CodeType.OVERDUE_TYPE);
        List<CodeInfo>  cardList= CodeInfoCache.getCodeInfo(CodeType.CARD_TYPE);
        List<CodeInfo>  boxList= CodeInfoCache.getCodeInfo(CodeType.BOX_TYPE);
        List<CodeInfo>  coopeateList= CodeInfoCache.getCodeInfo(CodeType.COOPEATE_DEGREE);
        List<CodeInfo>  interventionList= CodeInfoCache.getCodeInfo(CodeType.INTERVENTION_TYPE);
        List<CodeInfo>  feelList= CodeInfoCache.getCodeInfo(CodeType.FEEL_TYPE);
        List<CodeInfo>  dosageList= CodeInfoCache.getCodeInfo(CodeType.DOSAGE_SIZE);
        List<CodeInfo>  vipList= CodeInfoCache.getCodeInfo(CodeType.VIP_RAND);
        List<CodeInfo>  contactList= CodeInfoCache.getCodeInfo(CodeType.CONTACT_TIME_TYPE);
        List<CodeInfo>  problemList= CodeInfoCache.getCodeInfo(CodeType.PROBLEM_TYPE);
        List<CodeInfo>  sexList= CodeInfoCache.getCodeInfo(CodeType.SEX);
        List<CodeInfo>  sfgqList= CodeInfoCache.getCodeInfo(CodeType.YES_OR_NO);
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("会员一览表");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        HSSFCellStyle cellstyle = (HSSFCellStyle) wb.createCellStyle();// 设置表头样式
        cellstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置垂直居中
        cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置水平居中
        cellstyle.setWrapText(true); // 设置为自动换行
        HSSFFont cellFont = (HSSFFont) wb.createFont(); // 创建表头字体样式
        cellFont.setFontHeightInPoints((short) 10); // 设置字体大小
        cellFont.setFontName("宋体"); // 设置字体类型
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
        cellstyle.setFont(cellFont); // 为表头样式设置字体样式


        for (int i = 0; i < fieldName.size(); i++) {
            HSSFCell cell = row.createCell((short) i);
            cell.setCellValue((String) fieldName.get(i));
            cell.setCellStyle(cellstyle);
        }

        VipXxb vipXxb = null;

        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，

        for (int i = 0; i < fieldData.size(); i++) {
            row = sheet.createRow(i + 1);
            vipXxb = (VipXxb) fieldData.get(i);
            // 第四步，创建单元格，并设置值

            if (vipXxb.getCardno() == null) {
                row.createCell((short) 0).setCellValue("");
            } else {
                row.createCell((short) 0).setCellValue(vipXxb.getCardno());
            }
            if (vipXxb.getClientName() == null) {
                row.createCell((short) 1).setCellValue("");
            } else {
                row.createCell((short) 1).setCellValue(vipXxb.getClientName());
            }
            if (vipXxb.getPackageName() == null) {
                row.createCell((short) 2).setCellValue("");
            } else {
                row.createCell((short) 2).setCellValue(vipXxb.getPackageName());
            }
            if (vipXxb.getStartTime() == null) {
                row.createCell((short) 3).setCellValue("");
            } else {
                row.createCell((short) 3).setCellValue(vipXxb.getStartTime());
            }
            if (vipXxb.getEndTime() == null) {
                row.createCell((short) 4).setCellValue("");
            } else {
                row.createCell((short) 4).setCellValue(vipXxb.getEndTime());
            }
            if (vipXxb.getPackagePrice() == null) {
                row.createCell((short) 5).setCellValue("");
            } else {
                row.createCell((short) 5).setCellValue(vipXxb.getPackagePrice());
            }
            if (vipXxb.getVipId() == null) {
                row.createCell((short) 6).setCellValue("");
            } else {
                row.createCell((short) 6).setCellValue(vipXxb.getVipId());
            }
            if (vipXxb.getTjId() == null) {
                row.createCell((short) 7).setCellValue("");
            } else {
                row.createCell((short) 7).setCellValue(vipXxb.getTjId());
            }
            if (vipXxb.getMzId() == null) {
                row.createCell((short) 8).setCellValue("");
            } else {
                row.createCell((short) 8).setCellValue(vipXxb.getMzId());
            }
            if (vipXxb.getTjDate() == null) {
                row.createCell((short) 9).setCellValue("");
            } else {
                row.createCell((short) 9).setCellValue(vipXxb.getTjDate());
            }
            if (vipXxb.getProblem() == null) {
                row.createCell((short) 10).setCellValue("");
            } else {

                String problem = vipXxb.getProblem();
                if (StringUtils.isNotBlank(problem)) {

                        String[] array = problem.split(",");
                        String userName = "";
                        for (String str : array) {
                            for (CodeInfo c : problemList) {//汉化性别信息
                                if (c.getValue().equals( str + "")) {
                                    userName +=c.getContent() + ",";
                                    break;
                                }
                            }

                        }
                        row.createCell((short) 10).setCellValue(userName);

                }
//                row.createCell((short) 10).setCellValue(vipXxb.getProblem());
            }
            if (vipXxb.getMobile() == null) {
                row.createCell((short) 11).setCellValue("");
            } else {
                row.createCell((short) 11).setCellValue(vipXxb.getMobile());
            }
            if (vipXxb.getPromoteDoctor() == null) {
                row.createCell((short) 12).setCellValue("");
            } else {
                row.createCell((short) 12).setCellValue(vipXxb.getPromoteDoctor());
            }
            if (vipXxb.getVice() == null) {
                row.createCell((short) 13).setCellValue("");
            } else {
                row.createCell((short) 13).setCellValue(vipXxb.getVice());
            }
            if (vipXxb.getRequirements() == null) {
                row.createCell((short) 14).setCellValue("");
            } else {
                row.createCell((short) 14).setCellValue(vipXxb.getRequirements());
            }
            if (vipXxb.getContactName() == null) {
                row.createCell((short) 15).setCellValue("");
            } else {
                row.createCell((short) 15).setCellValue(vipXxb.getContactName());
            }
            if (vipXxb.getContactPhone() == null) {
                row.createCell((short) 16).setCellValue("");
            } else {
                row.createCell((short) 16).setCellValue(vipXxb.getContactPhone());
            }
            if (vipXxb.getContactTime() == null) {
                row.createCell((short) 17).setCellValue("");
            } else {

                for (CodeInfo c : contactList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getContactTime() + "")) {
                        row.createCell((short) 17).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 17).setCellValue(vipXxb.getContactTime());
            }
            if (vipXxb.getBox() == null) {
                row.createCell((short) 18).setCellValue("");
            } else {

                String problem = vipXxb.getBox();
                if (StringUtils.isNotBlank(problem)) {

                        String[] array = problem.split(",");
                        String userName = "";
                        for (String str : array) {
                            for (CodeInfo c : boxList) {//汉化性别信息
                                if (c.getValue().equals( str + "")) {
                                    userName +=c.getContent() + ",";
                                    break;
                                }
                            }

                        }
                        row.createCell((short) 18).setCellValue(userName);

                }
//                row.createCell((short) 18).setCellValue(vipXxb.getBox());
            }
            if (vipXxb.getMedication() == null) {
                row.createCell((short) 19).setCellValue("");
            } else {
                row.createCell((short) 19).setCellValue(vipXxb.getMedication());
            }
            if (vipXxb.getDrug() == null) {
                row.createCell((short) 20).setCellValue("");
            } else {

                for (CodeInfo c : dosageList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getDrug() + "")) {
                        row.createCell((short) 20).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 20).setCellValue(vipXxb.getDrug());
            }
            if (vipXxb.getFeel() == null) {
                row.createCell((short) 21).setCellValue("");
            } else {

                for (CodeInfo c : feelList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getFeel() + "")) {
                        row.createCell((short) 21).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 21).setCellValue(vipXxb.getFeel());
            }
            if (vipXxb.getAddress() == null) {
                row.createCell((short) 22).setCellValue("");
            } else {
                row.createCell((short) 22).setCellValue(vipXxb.getAddress());
            }
            if (vipXxb.getDetailedAddress() == null) {
                row.createCell((short) 23).setCellValue("");
            } else {
                row.createCell((short) 23).setCellValue(vipXxb.getDetailedAddress());
            }
            if (vipXxb.getNote() == null) {
                row.createCell((short) 24).setCellValue("");
            } else {
                row.createCell((short) 24).setCellValue(vipXxb.getNote());
            }
            if (vipXxb.getFgrDoctor() == null) {
                row.createCell((short) 25).setCellValue("");
            } else {
                row.createCell((short) 25).setCellValue(vipXxb.getFgrDoctor());
            }
            if (vipXxb.getMeetTime() == null) {
                row.createCell((short) 26).setCellValue("");
            } else {
                row.createCell((short) 26).setCellValue(vipXxb.getMeetTime());
            }
            if (vipXxb.getMeetType() == null) {
                row.createCell((short) 27).setCellValue("");
            } else {

                for (CodeInfo c : interventionList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getMeetType() + "")) {
                        row.createCell((short) 27).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 27).setCellValue(vipXxb.getMeetType());
            }
            if (vipXxb.getFirstTime() == null) {
                row.createCell((short) 28).setCellValue("");
            } else {
                row.createCell((short) 28).setCellValue(vipXxb.getFirstTime());
            }
            if (vipXxb.getZgDoor() == null) {
                row.createCell((short) 29).setCellValue("");
            } else {
                row.createCell((short) 29).setCellValue(vipXxb.getZgDoor());
            }
            if (vipXxb.getSyDoor() == null) {
                row.createCell((short) 30).setCellValue("");
            } else {
                row.createCell((short) 30).setCellValue(vipXxb.getSyDoor());
            }
            if (vipXxb.getObligateA() == null) {
                row.createCell((short) 31).setCellValue("");
            } else {
                for (CodeInfo c : sfgqList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getObligateA() + "")) {
                        row.createCell((short) 31).setCellValue(c.getContent());
                        break;
                    }
                }

//                row.createCell((short) 31).setCellValue(vipXxb.getObligateA());
            }
            if (vipXxb.getCooperationDegree() == null) {
                row.createCell((short) 32).setCellValue("");
            } else {
                for (CodeInfo c : coopeateList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getCooperationDegree() + "")) {
                        row.createCell((short) 32).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 32).setCellValue(vipXxb.getCooperationDegree());
            }
            if (vipXxb.getLastDate() == null) {
                row.createCell((short) 33).setCellValue("");
            } else {
                row.createCell((short) 33).setCellValue(vipXxb.getLastDate());
            }
            if (vipXxb.getProbability() == null) {
                row.createCell((short) 34).setCellValue("");
            } else {

                for (CodeInfo c : vipList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getProbability() + "")) {
                        row.createCell((short) 34).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 34).setCellValue(vipXxb.getProbability());
            }
            if (vipXxb.getTurnCard() == null) {
                row.createCell((short) 35).setCellValue("");
            } else {
                for (CodeInfo c : cardList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getTurnCard() + "")) {
                        row.createCell((short) 35).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 35).setCellValue(vipXxb.getTurnCard());
            }
            if (vipXxb.getDosage() == null) {
                row.createCell((short) 36).setCellValue("");
            } else {

                for (CodeInfo c : dosageList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getDosage() + "")) {
                        row.createCell((short) 36).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 36).setCellValue(vipXxb.getDosage());
            }
            if (vipXxb.getAfterFeeling() == null) {
                row.createCell((short) 37).setCellValue("");
            } else {

                for (CodeInfo c : feelList) {//汉化性别信息
                    if (c.getValue().equals(vipXxb.getAfterFeeling() + "")) {
                        row.createCell((short) 37).setCellValue(c.getContent());
                        break;
                    }
                }
//                row.createCell((short) 37).setCellValue(vipXxb.getAfterFeeling());
            }
            if (vipXxb.getTransferRecord() == null) {
                row.createCell((short) 38).setCellValue("");
            } else {
                row.createCell((short) 38).setCellValue(vipXxb.getTransferRecord());
            }
            if (vipXxb.getTransferAttention() == null) {
                row.createCell((short) 39).setCellValue("");
            } else {
                row.createCell((short) 39).setCellValue(vipXxb.getTransferAttention());
            }


        }
        //自适应列宽
        for (int i = 0; i < fieldName.size(); i++) {
            sheet.autoSizeColumn((short) i);
        }


        return wb;
    }


    //将信息写入输出流的方法。
    public void exportExcel(List fieldName, List fieldData, OutputStream os) throws Exception {
        HSSFWorkbook wb = createWorkbook(fieldName, fieldData);
        wb.write(os);
        os.close();
    }
}